﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Threading;
using MySql.Data.Types;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.IO;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Reflection;
using Region.Utils;
// Для подключения к MYSQL




// Для подключения к MSSQL ЕГРИП ЕГРЮЛ

namespace Region
{   
  public partial class Engine: IDisposable
  {
    
    public static String RegionStringToString(String S) { return S.Replace("&quot;", "\""); }
    public static String StringToRegionString(String S) { return S.Replace("\"", "&quot;"); }
    public BD_IP bd_IP;
    public BD_FIO bd_FIO = null;
    Boolean m_PDnAccess=false;
    public Boolean PDnAccess{get{return m_PDnAccess;}}
    Boolean m_FIOAccess=false;
    public Boolean FIOAccess{get{return m_FIOAccess;}}
    public static DateTime IntToRegionDate(Int32 RegionDate)
    {
      DateTime d = new DateTime(1970, 1, 1);
      return d.AddDays((Int32)(RegionDate / (3600 * 24)));
    }
    public static Int32 RegionDateToInt(DateTime RegionDate) { return (RegionDate - new DateTime(1970, 1, 1)).Days * 3600 * 24; }
    /// <summary>Считывает весь файл в буфер если файл сжат gz то разархивирует его</summary><param name="FileName"></param><returns></returns>
    static public byte[] ReadAllFile(String FileName)
    {
      FileInfo file = new FileInfo(FileName);
      using (Stream f1 = (file.Extension.ToLower() == ".gz") ? (Stream)(new System.IO.Compression.GZipStream(File.OpenRead(file.FullName), System.IO.Compression.CompressionMode.Decompress)) : (Stream)(File.OpenRead(file.FullName)))
      {
        byte[] AllBuff = new byte[0];
        byte[] ReadBuff = new byte[1024 * 1024];
        Int32 Size = 0;
        while (true)
        {
          Int32 readSize = f1.Read(ReadBuff, Size, ReadBuff.Length);
          byte[] NewAllBuff = new byte[Size + readSize];
          Array.Copy(AllBuff, 0, NewAllBuff, 0, Size);
          Array.Copy(ReadBuff, 0, NewAllBuff, Size, readSize);
          AllBuff = NewAllBuff;
          if (readSize != ReadBuff.Length) return AllBuff;
        }
      }
    }
    private MySqlConnection m_Database;
    private User m_CurrentUser;
    public User CurrentUser { get { return m_CurrentUser; } set {m_CurrentUser = value;} }
    private Dictionary<Int32, Groupe> l_Groupe;
    public void DebugMessage(String Message)
    {
        return;
      try
      {
        FileStream FS = new FileStream("./debug.log", FileMode.Append);
        Message = "[" + DateTime.Now.ToString("dd.MM.yyyy hh:mm:ss.fffffff") + "] " + Message + Environment.NewLine;
        byte[] Data = Encoding.GetEncoding(1251).GetBytes(Message);
        FS.Write(Data, 0, Data.Length);
        FS.Close();
      }
      catch { }
    }
    //public MySqlConnection Database { get { return m_Database; } }
    public String Version{get
      {
        Version V = Assembly.GetExecutingAssembly().GetName().Version;
        DateTime D = (new DateTime(2000,1,1)).AddDays(V.Build).AddSeconds(V.Revision*2);
        return D.ToShortDateString()+" "+D.ToShortTimeString();
      }}
    public readonly Utils.EngineConfig config;
    public Engine(Utils.EngineConfig Config=null)
    {
      // Удалить файл лога
      
      //try{FileStream FS = new FileStream("./debug.log", FileMode.Create); FS.Close();}catch { }
      DebugMessage("Start");
      if (Config==null) 
        config = new Utils.EngineConfig();
      else
        config = Config;
      if (config.isValue<String>("BD_IP"))
        bd_IP = new BD_IP(this);
      l_Users = new Dictionary<Int32, User>();
      l_Groupe = new Dictionary<Int32, Groupe>();
      
      l_Kont = new Dictionary<Int32, Kont>();
      l_Plan = new Dictionary<Int32, Plan>();

          
    }

    public void Dispose()
    {
      taskManager.Dispose();
    }
    public Byte[] getRegionFile(String FileID,String FileExt)
    {
      try
      {
          return File.ReadAllBytes(AppDomain.CurrentDomain.BaseDirectory + "Template/" + FileID + "." + FileExt);
      }
      catch{return null;}
    }
    public delegate void EventHandlerPDnLogin(Engine engine);
    /// <summary> Событие происходящее если удалось подключится к базе ПДн IP</summary>
    public event EventHandlerPDnLogin OnPDnLogin;
    public delegate void EventHandlerFIOLogin(Engine engine);
    /// <summary> Событие происходящее если удалось подключится к базе Обезличиваия</summary>
    public event EventHandlerFIOLogin OnFIOLogin;
    /// <summary> Проверить подключение к защищённой базе ИП(запуская ется при залогинивании в отдельном потоке)</summary>
    public void PDnLogin()
    {
      try
      {
        if ((from t in bd_IP.IP where t.INN=="---" && t.OGRN=="---" select t).Count()==0)
        {
          m_PDnAccess = true;
          if (OnPDnLogin!=null) OnPDnLogin(this);
        }
      }
      catch(SqlException e)
      {
        m_PDnAccess = false;
      }
    }
    Thread PDnLoginThread;
    
    public void FIOLogin1()
    {
      try
      {
        var bd_FIO1 = new BD_FIO(config.getValue<String>("BD_FIO",""));
        if ((from t in bd_FIO1.Links where t.ID=="---" select t).Count()==0)
        {
          m_FIOAccess = true;
          bd_FIO = bd_FIO1;
          if (OnFIOLogin!=null) OnFIOLogin(this);
        }
      }
      catch(SqlException )
      {
      }
    }
    public void FIOLogin2()
    {
      try
      {
        var bd_FIO1 = new BD_FIO(config.getValue<String>("BD_FIO2",""));
        if ((from t in bd_FIO1.Links where t.ID=="---" select t).Count()==0)
        {
          m_FIOAccess = true;
          bd_FIO = bd_FIO1;
          if (OnFIOLogin!=null) OnFIOLogin(this);
        }
      }
      catch(SqlException )
      {
      }
    }    
    Thread FIOLoginThread1;
    Thread FIOLoginThread2;
    // HACK Функция Registrator требуется только на момент перехода пользователей на новую версию, после этого её необходимо удаить а также удалить одноименного пользователя и базы данных
    bool Registrator(String UserName, String Pass)
    {
      try
      {
        String dbString = String.Format("Database=rpn;Data Source=192.168.0.19;User Id={0};Password={1};UseCompression=true;charset=utf8", "Registrator","9zYRYnJFeGYNwpNA");
        m_Database = new MySqlConnection(dbString);
        m_Database.Open();
        string hash = BitConverter.ToString(MD5.Create().ComputeHash(Encoding.UTF8.GetBytes(UserName.ToUpper()+Pass))).Replace("-","").ToLower();
        SQLResult r = SQLSelect("SELECT UserID FROM users WHERE Name=@Name AND Pass=@Pass",new SQLParam() { { "@Name", UserName },{"@Pass",hash} });
        if (r.Count>0)
        {
          try{SQLEdit("CREATE USER @Name @'%' IDENTIFIED BY @Pass",new SQLParam() { { "@Name", UserName.ToLower() },{"@Pass",Pass.ToLower()} });}catch(ExceptionRegionSQLError ){/*Проигнорировать ошибку так как такой пользователь создан уже*/}
          try{SQLEdit("GRANT USAGE ON * . * TO @Name @'%' IDENTIFIED BY @Pass WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0",new SQLParam() { { "@Name", UserName.ToLower() },{"@Pass",Pass} });}catch(ExceptionRegionSQLError ){/*Проигнорировать ошибку так как такой пользователь создан уже*/}
          try{SQLEdit("GRANT ALL PRIVILEGES ON `rpn` . * TO @Name @'%'",new SQLParam() { { "@Name", UserName.ToLower() } });}catch(ExceptionRegionSQLError ){/*Проигнорировать ошибку так как такой пользователь создан уже*/}
          return true;
        }
      }
      finally
      {
        m_Database.Close();
        m_Database=null;
      }
      return false;
    }
    /// <summary> Подключится к базе данных программы регион, а также к остальным базам используемым в проэкте</summary><param name="UserName">Имя пользователя</param><param name="Pass">Пароль</param><returns>true еслиподключение удалось</returns>
    public bool Login(String UserName, String Pass)
    {
      try
      {
        
        //MD5 md5 = MD5.Create();
        String S = UserName.ToUpper() + Pass;
        //Установить ссылку на базу данных
        RegionDBLink = String.Format("Database=rpn;Data Source=192.168.0.19;User Id={0};Password={1};UseCompression=true;charset=utf8",
                                                       UserName.Replace(';', ' ').Replace('"', ' ').Replace("'", " ").ToLower(),
                                                       Pass.Replace(';', ' ').Replace('"', ' ').Replace("'", " ").ToLower());
        SQLResult R = SQLSelect("SELECT UserID FROM users  WHERE Name=@Name", new SQLParam() { { "@Name", UserName } });
        if (R.Count == 0) return false;
        Int32 UserID = (Int32)R[0]["UserID"];
        m_CurrentUser = getUser(UserID);
        return true;
        /*        m_Database = new MySqlConnection(RegionDBLink);
        m_Database.StateChange += OnStateChange;
        m_Database.Open();
        if (m_Database.State == System.Data.ConnectionState.Open)
        {
          m_Database.ChangeDatabase("rpn");
          // Проверить базу данных на совместимость и если есть права внести изменения
          SQLResult R = SQLSelect("SELECT UserID FROM users  WHERE Name=@Name", new SQLParam() { { "@Name", UserName } });

          if (R.Count == 0) return false;
          Int32 UserID = (Int32)R[0]["UserID"];
          m_CurrentUser = getUser(UserID);
          return true;
        }
        else
        {
          m_Database.Close();
          return false;
        }*/
        
      }
      catch (Exception E)
      {
        //m_Database.Close();
        if (Registrator(UserName, Pass)) 
          return Login(UserName,Pass);
        return false;
      }
    }
    public bool LinkDB(String connectString)
    {
      //Установить ссылку на базу данных
      RegionDBLink = connectString;
      return true;        
    }
    public String processHashUser(String UserName, String Pass)
    {
        return BitConverter.ToString(MD5.Create().ComputeHash(Encoding.UTF8.GetBytes(UserName.ToUpper() + Pass))).Replace("-", "");
    }
    public String getHashUser(String UserName,MySqlConnection Database=null)
    {
        SQLResult R = SQLSelect("SELECT Pass FROM users WHERE Name=@Name", new SQLParam() { { "@Name", UserName } }, Database);
        if (R.Count == 0)
            return null;
        return (String)R[0]["Pass"];
    }
    public bool connectToRegion()
    {
        try
        {            
            var Database = getRegionDatabase();            
            if (Database.State == System.Data.ConnectionState.Open)
            {
                Database.ChangeDatabase("rpn");
                m_Database = Database;
                m_Database.StateChange += OnStateChange;
                
                return true;
            }
            else
            {
                Database.Close();
                return false;
            }

        }
        catch (Exception E)
        {
            m_Database.Close();
            return false;
        }
    }
    public bool isLoginRegion()
    {
        if (m_Database == null) return false;
        if (m_Database.State != System.Data.ConnectionState.Open) return false;
        return true;
    }

    // Подключить дополнительные базы данных
    public bool LoginPDn()
    {
      PDnLoginThread = new Thread(PDnLogin);
      PDnLoginThread.Start();
      return true;
    }
    public bool LoginFIO()
    {
      m_FIOAccess = false;
      FIOLoginThread1 = new Thread(FIOLogin1);
      FIOLoginThread1.Start();
      FIOLoginThread2 = new Thread(FIOLogin2);
      FIOLoginThread2.Start();
      return true;
    }
    void OnStateChange(Object self, System.Data.StateChangeEventArgs e)
    {
    }
    Dictionary<DateTime,bool> Days;
    public Dictionary<DateTime,bool> getDays()
    {
      if (Days==null)
      {
        Days = new Dictionary<DateTime, Boolean>();
        foreach(var D in SQLSelect("SELECT Day FROM days"))
          Days[IntToRegionDate((Int32)D.Value["Day"])] = true;
      }
      return Days;
    }
    public class DirectInfo
    {
      public String Direct;
      public String ParentDirect;
      public String DirectName;
      public String DirectName2;
      public Int32 KontObjectDirect;
    }
    Dictionary<String,DirectInfo> Directs;
    public Dictionary<String,DirectInfo> getDirects()
    {
      if (Directs==null)
      {
        Directs = new Dictionary<String, DirectInfo>();
        foreach(var D in SQLSelect("SELECT Direct,ParentDirect, DirectName, DirectName2, KontObjectDirect FROM direct"))
        {
          var di = new DirectInfo();
          di.Direct = (String)D.Value["Direct"];
          di.ParentDirect = (String)D.Value["ParentDirect"];
          di.DirectName = (String)D.Value["DirectName"];
          di.DirectName2 = (String)D.Value["DirectName2"];
          di.KontObjectDirect = (Int32)D.Value["KontObjectDirect"];
          Directs[di.Direct] = di;
        }
      }
      return Directs;
    }
    public Groupe getGroupe(Int32 GroupeID)
    {
      if (l_Groupe.ContainsKey(GroupeID)) return (Groupe)l_Groupe[GroupeID];
      Groupe groupe = new Groupe(this, GroupeID);
      l_Groupe[GroupeID] = groupe;
      return groupe;
    }
    public class SQLEventArgs: EventArgs
    {
      public Int64 ElapsedMilliseconds;
      public String SQL;
      public SQLParam Params;
      public SQLResult Result;
      public Int32 NumLine;
    }
    public delegate void EventHandlerOnSQL(SQLEventArgs ea);    
    public event EventHandlerOnSQL OnSQL;
    public delegate void EventHandleShowTask();   
    public event EventHandleShowTask OnShowTask ;
    public void ShowTask()
    {
      if(OnShowTask!=null)
        OnShowTask.Invoke();
    }
    public bool isAccess(String Access){return m_CurrentUser.isAccess(Access);}
    MySqlCommand getCommand(String SQL,MySqlConnection Database) { return new MySqlCommand(SQL, Database); }
    Object _SQLlock = new Object();
    String RegionDBLink = null;
    public MySqlConnection getRegionDatabase()
    {
      var Database = new MySqlConnection(RegionDBLink);
      Database.Open();
      return Database;
    }
    public Int32 SQLEdit(String SQL, MySqlConnection Database=null) { return SQLEdit(SQL, new SQLParam(), Database); }
    public Int32 SQLEdit(String SQL, SQLParam Params, MySqlConnection Database=null) { MySqlCommand cmd; return SQLEdit(SQL, Params, out cmd, Database); }
    public Int32 SQLEdit(String SQL, SQLParam Params, out MySqlCommand cmd, MySqlConnection Database=null)
    {
      var sw = new System.Diagnostics.Stopwatch();
      Int32 NumLine=0;
      MySqlConnection local_db=null;
      try
      {
        sw.Start();
        // Проверить есть ли постоянное подключение
        if (Database == null)
        {
            Database = m_Database;
        }
        // Подключится только для этого запроса
        if (Database == null)
        {
            local_db = getRegionDatabase();
            Database = local_db;
        }      
        cmd = getCommand(SQL,Database);
        foreach (KeyValuePair<String, Object> P in Params) cmd.Parameters.AddWithValue(P.Key, P.Value);
        NumLine = cmd.ExecuteNonQuery();
        return NumLine;
      }
      catch (Exception E)
      {
        throw new ExceptionRegionSQLError(this,"Ошибка при выполнении запроса",SQL,Params,Database);
      }
      finally
      {
        if (local_db!=null)
          local_db.Close();
        sw.Stop();
        if (OnSQL!=null)
        {
          var ea = new SQLEventArgs();
          ea.ElapsedMilliseconds = sw.ElapsedMilliseconds;
          ea.SQL = SQL;
          ea.NumLine = NumLine;
          ea.Params = Params;
          ea.Result = null;
          OnSQL(ea);
        }
      }
    }
    public SQLResult SQLSelect(String SQL, MySqlConnection Database=null) { return SQLSelect(SQL, new SQLParam()); }
    public SQLResult SQLSelect(String SQL, SQLParam Params, MySqlConnection Database=null)
    {
      MySqlConnection local_db=null;      
      SQLResult Data = new SQLResult();
      MySqlDataReader r = null;
      var sw = new System.Diagnostics.Stopwatch();
      sw.Start();
      DebugMessage("Выполняется запрос: " + SQL);
      try
      {
        // Проверить есть ли постоянное подключение
        if (Database == null)
        {
            Database = m_Database;
        }
        // Подключится только для этого запроса
        if (Database == null)
        {
          local_db = getRegionDatabase();
          Database = local_db;
        }                  
        MySqlCommand cmd = getCommand(SQL,Database);
        foreach (KeyValuePair<String, Object> P in Params) 
          cmd.Parameters.AddWithValue(P.Key, P.Value);
        r = cmd.ExecuteReader();
        Int32 RowN = 0;
        String[] Names = new String[r.FieldCount];
        for (Int32 i = 0; i < r.FieldCount; i++) Names[i] = r.GetName(i);
        Type MSDT = new DateTime().GetType();
        while (r.Read())
        {
          Dictionary<String, Object> Row = new Dictionary<String, Object>();
          for (Int32 i = 0; i < Names.Length; i++)
          {
            if (r.GetFieldType(i) == MSDT)
            {
              try
              {
                MySqlDateTime D = r.GetMySqlDateTime(i);
                if (D.Year==0|| D.Month==0|| D.Day==0)
                  Row[Names[i]] = new RegionDate();
                else
                  Row[Names[i]] = new RegionDate(D.Year, D.Month, D.Day, D.Hour, D.Minute, D.Second, D.Millisecond);
              }
              catch(SqlTypeException)
              {
                Row[Names[i]] = new RegionDate();
              }              
            }
            else
              Row[Names[i]] = r.GetValue(i);
          }
          Data[RowN++] = Row;
        }
        DebugMessage("Запрос выполнен, возвращено строк:" + Data.Count.ToString() + ", столбцов:" + Names.Length.ToString());
        return Data;
      }
      catch (SqlException E)
      {
        DebugMessage("(MSB)Ошибка при выполнеии запроса" + Environment.NewLine + E.Message);
        throw new ExceptionRegionSQLError(this,"Ошибка при выполнении запроса",SQL,Params,Database);
      }
      finally
      {
        if (local_db!=null)
          local_db.Close();
        sw.Stop();
        if (OnSQL!=null)
        {
          var ea = new SQLEventArgs();
          ea.ElapsedMilliseconds = sw.ElapsedMilliseconds;
          ea.SQL = SQL;
          ea.NumLine = Data.Count;
          ea.Params = Params;
          ea.Result = Data;
          OnSQL(ea);
        }
        if (r != null)
          r.Close();
      }
      
    }
    static public SQLResult SQLSelect(String SQL, SQLParam Params, SqlConnection Database)
    {
      SQLResult Data = new SQLResult();
      SqlDataReader read = null;
      
      try
      {
        var cmd = new SqlCommand(SQL, Database);
        foreach (var V in Params) 
          cmd.Parameters.AddWithValue(V.Key, V.Value);
        read = cmd.ExecuteReader(); 
        Int32 RowN=0;
        while (read.Read())
        {
          Dictionary<String, Object> Row = new Dictionary<String, Object>();
          for(Int32 i=0;i<read.FieldCount;i++)
          {
            String TypeName = read.GetFieldType(i).Name;
            if (TypeName=="String")
            {
              if (!read.IsDBNull(i)) 
                Row[read.GetName(i)] = read.GetString(i); 
              else 
                Row[read.GetName(i)] = "";
            }
            else if (TypeName=="DateTime")
            {
              if (!read.IsDBNull(i)) 
                Row[read.GetName(i)] = new RegionDate(read.GetDateTime(i)); 
              else 
                Row[read.GetName(i)] =  new RegionDate();
            }
            else
              Row[read.GetName(i)] = read.GetString(i);
          }
          Data[RowN++] = Row;
        }
      }
      finally
      { 
        if (read!=null) 
          read.Close();
      }
      return Data;
    }
    ~Engine() { if (m_Database != null) m_Database.Close(); }
    public static String getAddressString(String PostIndex, String Region, String Raion, String Gorod, String Naspunkt, String Street, String Dom, String Korpus, String Kvart)
    {
      List<String> L = new List<String>();
      PostIndex = PostIndex.Trim();
      if (PostIndex != "")
      {
        if (Regex.IsMatch(PostIndex, "^[0-9]{6,6}$", RegexOptions.IgnoreCase)) L.Add(PostIndex);
      }
      Region = Region.Trim();
      if (Region != "")
      {
        if (Regex.IsMatch(Gorod, "москва", RegexOptions.IgnoreCase)) Region = "г. Москва";
        if (Regex.IsMatch(Gorod, "хабаровск", RegexOptions.IgnoreCase)) Region = "Хабаровский край";
        L.Add(Region);
      }
      Raion = Raion.Trim();
      if (Raion != "")
      {
        // Если с качестве района указан город Хабаровск
        Raion = Regex.Replace(Raion, "хабаровск", "Хабаровск", RegexOptions.IgnoreCase);
        if (Raion == "Хабаровск") Raion = "г. Хабаровск";
        if (!L.Contains(Raion)) L.Add(Raion);
      }
      Gorod = Gorod.Trim();
      if (Gorod != "")
      {
        String Gorod2 = "";
        String[] P = new String[] { "г" };
        String[] PB = new String[] { "г" };
        Gorod = Regex.Replace(Gorod, "москва", "Москва", RegexOptions.IgnoreCase);
        Gorod = Regex.Replace(Gorod, "хабаровск", "Хабаровск", RegexOptions.IgnoreCase);
        bool E = false;
        String pat;
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @"$"; if (Regex.IsMatch(Gorod, pat, RegexOptions.IgnoreCase)) { Gorod2 = PB[i] + ". " + Regex.Replace(Gorod, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @".$"; if (Regex.IsMatch(Gorod, pat, RegexOptions.IgnoreCase)) { Gorod2 = PB[i] + ". " + Regex.Replace(Gorod, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @"\s"; if (Regex.IsMatch(Gorod, pat, RegexOptions.IgnoreCase)) { Gorod2 = PB[i] + ". " + Regex.Replace(Gorod, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @".\s"; if (Regex.IsMatch(Gorod, pat, RegexOptions.IgnoreCase)) { Gorod2 = PB[i] + ". " + Regex.Replace(Gorod, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) Gorod2 = "г. " + Gorod;
        if (!L.Contains(Gorod2)) L.Add(Gorod2);
      }

      Naspunkt = Naspunkt.Trim();
      if (Naspunkt != "")
      {
        String Naspunkt2 = "";
        String[] P = new String[] { "с", "п", "пгт", "рп", "дп", "ст", "п/ст", "п/р", "уч-к" };
        String[] PB = new String[] { "с", "п", "пгт", "рп", "дп", "ст", "п/ст", "п/р", "уч-к" };
        Naspunkt = Regex.Replace(Naspunkt, "хабаровск", "Хабаровск", RegexOptions.IgnoreCase);
        bool E = false;
        String pat;
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @"$"; if (Regex.IsMatch(Naspunkt, pat, RegexOptions.IgnoreCase)) { Naspunkt2 = PB[i] + ". " + Regex.Replace(Naspunkt, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @".$"; if (Regex.IsMatch(Naspunkt, pat, RegexOptions.IgnoreCase)) { Naspunkt2 = PB[i] + ". " + Regex.Replace(Naspunkt, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @"\s"; if (Regex.IsMatch(Naspunkt, pat, RegexOptions.IgnoreCase)) { Naspunkt2 = PB[i] + ". " + Regex.Replace(Naspunkt, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @".\s"; if (Regex.IsMatch(Naspunkt, pat, RegexOptions.IgnoreCase)) { Naspunkt2 = PB[i] + ". " + Regex.Replace(Naspunkt, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) Naspunkt2 = Naspunkt;
        if (!L.Contains(Naspunkt2)) L.Add(Naspunkt2);
      }
      Street = Street.Trim();
      if (Street != "")
      {
        String Street2 = "";
        String[] P = new String[] { "с", "п", "пгт", "рп", "дп", "ст", "п/ст", "п/р", "уч-к" };
        String[] PB = new String[] { "с", "п", "пгт", "рп", "дп", "ст", "п/ст", "п/р", "уч-к" };

        bool E = false;
        String pat;
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @"$"; if (Regex.IsMatch(Street, pat, RegexOptions.IgnoreCase)) { Street2 = PB[i] + ". " + Regex.Replace(Street, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"\s" + P[i] + @".$"; if (Regex.IsMatch(Street, pat, RegexOptions.IgnoreCase)) { Street2 = PB[i] + ". " + Regex.Replace(Street, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @"\s"; if (Regex.IsMatch(Street, pat, RegexOptions.IgnoreCase)) { Street2 = PB[i] + ". " + Regex.Replace(Street, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) for (int i = 0; i < P.Length; i++) { pat = @"^" + P[i] + @".\s"; if (Regex.IsMatch(Street, pat, RegexOptions.IgnoreCase)) { Street2 = PB[i] + ". " + Regex.Replace(Street, pat, "", RegexOptions.IgnoreCase); E = true; break; } }
        if (!E) Street2 = "ул. " + Street;
        L.Add(Street2);
      }
      Dom = Dom.Trim();
      if (Dom != "")
      {
        String Dom2 = "";
        Dom2 += Dom;
        if (Korpus != "") { Dom2 += "-" + Korpus; }
        L.Add("д. " + Dom2);
      }
      Kvart = Kvart.Trim();
      if (Kvart != "") L.Add(Kvart);
      return String.Join(", ", L.ToArray());
    }
    public enum EDictNames { FromType, OsnovFull, Osnov, UserFIO, Article, ArticleNum,  KontDirectZPP, KontDirect, KontType, KontForm, Raions, ExternName, Measure, MeasureSud, OspName, ReportTemplateName, ReportTemplateClass, Service, TaskVCP, OID };
    private Dictionary<EDictNames,Dictionary<Int32, String>> m_dicts = new Dictionary<EDictNames,Dictionary<Int32, String>>();
    private Dictionary<EDictNames,List<String>> m_dictConfig = null;
    public Dictionary<Int32, String> getDict(EDictNames dict)
    {
      if (m_dictConfig==null)
      {
        m_dictConfig = new Dictionary<EDictNames,List<String>>()
        {
          {EDictNames.FromType, new List<String>(){"FromTypeID", "FromType",    "SELECT FromTypeID, FromType FROM fromtype WHERE 1","[Не указан]"}},
          {EDictNames.OsnovFull,new List<String>(){"OsnovID",    "OsnovFull",   "SELECT OsnovID, OsnovFull FROM osnov WHERE 1","[Не указан]"}},
          {EDictNames.Osnov,new List<String>()    {"OsnovID",    "Osnov",       "SELECT OsnovID, Osnov FROM osnov WHERE 1","[Не указан]"}},
          {EDictNames.UserFIO,new List<String>()  {"UserID",     "FIO",         "SELECT UserID, FIO FROM users WHERE 1","[Не указан]"}},
          {EDictNames.Article,new List<String>()  {"ArticleID",  "Article",     "SELECT ArticleID, Article FROM article WHERE 1 ORDER BY ArticleNum",""}},          
          {EDictNames.ArticleNum,new List<String>()  {"ArticleID",  "ArticleNum",     "SELECT ArticleID, ArticleNum FROM article WHERE 1 ORDER BY ArticleNum",""}},                    
          {EDictNames.KontDirectZPP,new List<String>()  {"ZPP",  "ZppName",     "SELECT ZPP, ZppName FROM zpp WHERE 1","[Не выбрано]"}},
          {EDictNames.KontDirect,new List<String>()  {"KontObjectDirect", "KontObjectDirectName",   "SELECT KontObjectDirect, KontObjectDirectName FROM kontdirect WHERE 1","[Не выбрано]"}},
          {EDictNames.KontType,new List<String>()  {"KontTypeID",  "KontType",  "SELECT KontTypeID, KontType FROM konttype WHERE 1","[Не выбрано]"}},
          {EDictNames.KontForm,new List<String>()  {"KontForm",  "KontFormName","SELECT KontForm, KontFormName FROM kontform WHERE 1","[Не выбрано]"}},
          {EDictNames.Raions,new List<String>()  {"RaionID",  "Raion",          "SELECT RaionID, Raion FROM raion WHERE 1","[Не выбрано]"}},
          {EDictNames.ExternName,new List<String>()  {"ExternNameID",  "ExternName", "SELECT ExternNameID, ExternName FROM externname WHERE 1",""}},
          {EDictNames.Measure,new List<String>()  {"MeasureID",  "Measure",      "SELECT MeasureID, Measure FROM measure WHERE 1","[Не выбрано]"}},
          {EDictNames.MeasureSud,new List<String>()  {"MeasureSudID",  "MeasureSud", "SELECT MeasureSudID, MeasureSud FROM measuresud WHERE 1","[Не выбрано]"}},
		  {EDictNames.OspName,new List<String>()  {"OspNameID",  "OspName", "SELECT OspNameID, OspName FROM ospname WHERE 1","[Не выбрано]"}},
		  {EDictNames.ReportTemplateName,new List<String>()  {"ReportTemplateID",  "ReportTemplateName", "SELECT ReportTemplateID, ReportTemplateName FROM reporttemplate WHERE 1"}},
		  {EDictNames.ReportTemplateClass,new List<String>()  {"ReportTemplateID",  "ReportTemplateClass", "SELECT ReportTemplateID, ReportTemplateClass FROM reporttemplate WHERE 1"}},
          {EDictNames.Service,new List<String>()  {"ServiceID",  "Service", "SELECT ServiceID, Service FROM service WHERE 1 ORDER BY ServiceID"}},
          {EDictNames.TaskVCP,new List<String>()  {"TaskVCPID",  "TaskVCP", "SELECT TaskVCPID, TaskVCP FROM taskvcp WHERE 1 ORDER BY TaskVCPID"}},
          {EDictNames.OID,new List<String>()  {"OtdelID",  "OtdelName", "SELECT OtdelID, OtdelName FROM otdel WHERE `TO`=1","[Не выбрано]"}},
          
        };
      }
      if (!m_dicts.ContainsKey(dict))
      {
        var d = new Dictionary<Int32, String>();
        SQLResult R = SQLSelect(m_dictConfig[dict][2]);
        String ID = m_dictConfig[dict][0];
        String Pole = m_dictConfig[dict][1];
        if (m_dictConfig[dict].Count>3 && m_dictConfig[dict][3]!=null) d[0] = m_dictConfig[dict][3];
        for (int i = 0; i < R.Count; i++)
          d[(Int32)R[i][ID]] = Engine.RegionStringToString((String)R[i][Pole]);
        m_dicts[dict] = d;
      }
      return new Dictionary<Int32, String>(m_dicts[dict]);
      
    }
    public Dictionary<Int32, String> FromType{get{return getDict(EDictNames.FromType);}}
    public Dictionary<Int32, String> OsnovFull{get{return getDict(EDictNames.OsnovFull);}}
    public Dictionary<Int32, String> Osnov{get{return getDict(EDictNames.Osnov);}}
    public Dictionary<Int32, String> UserFIO{get{return getDict(EDictNames.UserFIO);}}
    public Dictionary<Int32, String> Article{get{return getDict(EDictNames.Article);}}
    public Dictionary<Int32, String> ArticleNum { get { return getDict(EDictNames.ArticleNum); } }    
    public Dictionary<Int32, String> KontDirectZPP{get{return getDict(EDictNames.KontDirectZPP);}}
    public Dictionary<Int32, String> KontDirect{get{return getDict(EDictNames.KontDirect);}}
    public Dictionary<Int32, String> KontType{get{return getDict(EDictNames.KontType);}}
    public Dictionary<Int32, String> KontForm{get{return getDict(EDictNames.KontForm);}}
    public Dictionary<Int32, String> Raions{get{return getDict(EDictNames.Raions);}}
    public Dictionary<Int32, String> ExternName{get{return getDict(EDictNames.ExternName);}}
    public Dictionary<Int32, String> Measure{get{return getDict(EDictNames.Measure);}}
    public Dictionary<Int32, String> MeasureSud{get{return getDict(EDictNames.MeasureSud);}}
    public Dictionary<Int32, String> OspName{get{return getDict(EDictNames.OspName);}}
    public Dictionary<Int32, String> ReportTemplateName{get{return getDict(EDictNames.ReportTemplateName);}}
    public Dictionary<Int32, String> ReportTemplateClass{get{return getDict(EDictNames.ReportTemplateClass);}}
    public Dictionary<Int32, String> Service{get{return getDict(EDictNames.Service);}}
    public Dictionary<Int32, String> TaskVCP{get{return getDict(EDictNames.TaskVCP);}}
    public Dictionary<Int32, String> OID{get{return getDict(EDictNames.OID);}}

    public struct TLawStInfo { public String LawStID, LawID,LawStName;}
    Dictionary<String, TLawStInfo> m_LawSt = null;
    public Dictionary<String, TLawStInfo> LawSt
    {
      get
      {
        if(m_LawSt==null)
        {
          m_LawSt = new Dictionary<String, TLawStInfo>();
          SQLResult r = SQLSelect("SELECT LawStID, LawStName,LawID FROM lawst WHERE 1");
          for(Int32 i=0;i<r.Count;i++)
          {
            var info = new TLawStInfo();
            info.LawStID = (String)r[i]["LawStID"];
            info.LawStName = (String)r[i]["LawStName"];
            info.LawID = (String)r[i]["LawID"];
            m_LawSt[(String)r[i]["LawStID"]] = info;
          }
        }
        return new Dictionary<String, TLawStInfo>(m_LawSt);
      }
    }
    public struct TReportTemplate 
    {
        public Int32 ReportTemplateID;
        public String ReportTemplateName;
        public String ReportTemplateParam;
        public String ReportTemplateClass;
    }
    Dictionary<Int32, TReportTemplate> m_ReportTemplate = null;
    public Dictionary<Int32, TReportTemplate> ReportTemplate
    {
        get
        {
            if (m_ReportTemplate == null)
            {
                m_ReportTemplate = new Dictionary<Int32, TReportTemplate>();
                SQLResult r = SQLSelect("SELECT ReportTemplateID, ReportTemplateName, ReportTemplateParam, ReportTemplateClass FROM reporttemplate WHERE 1");
                for (Int32 i = 0; i < r.Count; i++)
                {
                    var info = new TReportTemplate();
                    info.ReportTemplateID = (Int32)r[i]["LawStID"];
                    info.ReportTemplateName = (String)r[i]["ReportTemplateName"];
                    info.ReportTemplateParam = (String)r[i]["ReportTemplateParam"];
                    info.ReportTemplateClass = (String)r[i]["ReportTemplateClass"];
                    m_ReportTemplate[info.ReportTemplateID] = info;
                }
            }
            return new Dictionary<Int32, TReportTemplate>(m_ReportTemplate);
        }
    }    
    static Dictionary<Int32, String> m_CheckType = new Dictionary<int, string>{{0,"[Не указан]"},{1,"Внеплановая"},{2,"Плановая"},{3,"Административное раследование"},{4,"Внешний протокол"},{5,"Непосредственное обнаружение"},{6,"Эпид. расследование"}};
    public Dictionary<Int32, String> CheckType{get{return new Dictionary<Int32, String>(m_CheckType);}}
    public void addExternName(String ExternName)
    {
      ExternName = ExternName.Trim();
      if (SQLEdit("INSERT INTO externname(ExternName) VALUES (@ExternName)",new SQLParam(){{"@ExternName", ExternName}})>0)
        m_dicts.Remove(EDictNames.ExternName);
    }
    public void addOspName(String OspName)
    {
      OspName = OspName.Trim();
      if (SQLEdit("INSERT INTO ospname(OspName) VALUES (@OspName)",new SQLParam(){{"@OspName", OspName}})>0)
        m_dicts.Remove(EDictNames.OspName);
    }
    public struct TOkvedInfo { public Int32 OkvedID, ParentOkvedID; public String OkvedName, OkvedTitle, OkvedInfo;}
    private Dictionary<Int32, TOkvedInfo> m_Okved;
    public Dictionary<Int32, TOkvedInfo> Okved
    {
      get
      {
        if (m_Okved == null)
        {
          SQLResult R = SQLSelect("SELECT * FROM okved WHERE 1");
          m_Okved = new Dictionary<int, TOkvedInfo>();
          for (int i = 0; i < R.Count; i++)
          {
            TOkvedInfo OI = new TOkvedInfo();
            OI.OkvedID = (Int32)R[i]["OkvedID"];
            OI.ParentOkvedID = (Int32)R[i]["ParentOkvedID"];
            OI.OkvedInfo = (String)R[i]["OkvedInfo"];
            OI.OkvedName = (String)R[i]["OkvedName"];
            OI.OkvedTitle = (String)R[i]["OkvedTitle"];
            m_Okved[OI.OkvedID] = OI;
          }
        }
        return new Dictionary<Int32, TOkvedInfo>(m_Okved);
      }
    }
    public struct TForm112Info
    {
      public String Form112ID, ParentF112ID;
      public String Form112;
      public object ItemID { get{return Form112ID;} }
      public object ItemParentID { get{return ParentF112ID;} }
      public String ItemCaption { get{return Form112;} }
    }
    private Dictionary<String, TForm112Info> m_Form112;
    public Dictionary<String, TForm112Info> Form112
    {
      get
      {
        if (m_Form112 == null)
        {
          SQLResult R = SQLSelect("SELECT * FROM form112 WHERE 1");
          m_Form112 = new Dictionary<String, TForm112Info>();
          for (int i = 0; i < R.Count; i++)
          {
            var FI = new TForm112Info();
            FI.Form112 = (String)R[i]["Form112"];
            FI.Form112ID = (String)R[i]["Form112ID"];
            FI.ParentF112ID = (String)R[i]["ParentF112ID"];
            m_Form112[FI.Form112ID] = FI;
          }
        }
        return new Dictionary<String, TForm112Info>(m_Form112);
      }
    }
    public Dictionary<String, Object> Form112forTree
    {
      get
      {
        var list = new Dictionary<String, Object>();
        foreach (var i in Form112) list[i.Key] = i.Value;
        return list;
      }
    }
    private Dictionary<String, String> m_Form112_Form112;
    public Dictionary<String, String> Form112_Form112
    {
      get
      {
        if (m_Form112_Form112 == null)
        {
          SQLResult R = SQLSelect("SELECT * FROM form112_form112 WHERE 1");
          m_Form112_Form112 = new Dictionary<String, String>();
          for (int i = 0; i < R.Count; i++)
          {
            String Form112ID = (String)R[i]["Form112ID"];
            String ParentF112ID = (String)R[i]["ParentForm112ID"];
            m_Form112_Form112[ParentF112ID] = Form112ID;
          }
        }
        return new Dictionary<String, String>(m_Form112_Form112);
      }
    }
    /*private Dictionary<Int32,String> m_Form112_Article;
    public Dictionary<Int32,String> Form112_Article
    {
      get
      {
        if (Form112_Article == null)
        {
          SQLResult R = SQLSelect("SELECT * FROM form112_article WHERE 1");
          Form112_Article = new Dictionary<Int32,String >();
          for (int i = 0; i < R.Count; i++)
          {
            String Form112ID = (String)R[i]["Form112ID"];
            Int32 ArticleID = (Int32)R[i]["ArticleID"];
            Form112_Article[ArticleID] = Form112ID;
          }
        }
        return new Dictionary<String, Int32>(Form112_Article);
      }
    }*/
  }
  public class SQLResult : Dictionary<Int32, Dictionary<String, Object>> { };
  public class SQLParam : Dictionary<String, Object> { };
  public enum EPoleType {Void=0, Int32, Int64, RegionDate, Date,TimeStamp, String, Bool };
  public class ExceptionRegion: Exception
  {
    Engine m_engine;
    public Engine engine{get{return m_engine;}}
    public ExceptionRegion(Engine engine,String message): base(message){m_engine=engine;}
  }
  /// <summary> Это исключение вызывае при ошибке выполнения запроса</summary>
  public class ExceptionRegionSQLError: ExceptionRegion
  {
    String m_SQL;
    SQLParam m_Params;
    MySqlConnection m_Database;
    public String SQL{get{return m_SQL;}}
    public SQLParam Params{get{return m_Params;}}
    public MySqlConnection Database{get{return m_Database;}}
    public ExceptionRegionSQLError(Engine engine,String message,String SQL, SQLParam Params, MySqlConnection Database):base(engine,message){m_SQL=SQL; m_Params=Params; m_Database=Database;}
  };
  /// <summary> Это исключение вызывается при невозможности обработать данные перечисленных типов в EPoleType</summary>
  public class ExceptionRegionEPoleType: ExceptionRegion
  {
    EPoleType m_Type;
    public EPoleType Type{get{return m_Type;}}
    public ExceptionRegionEPoleType(Engine engine,String message,EPoleType Type):base(engine,message){m_Type=Type;}
  };

  
  public partial class Engine
  {
  	TaskManager m_taskManager = null;
  	public TaskManager taskManager{get
    {
        if(m_taskManager==null)
            m_taskManager = new TaskManager(1);
        return m_taskManager;
    }}
  }
  
}
